﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using System.Reflection;

namespace DBHelper
{
    public class AutoNum
    {
        public virtual int AutoId { get; set; }
    }
    public static class BaseBusiness
    {
        public static string connectionName = "strSqlServerCe";
        public static DbBase dbs = new DbBase(connectionName);

        public static bool CleanCache()
        {
            DesCache.RemoveAllCache();
            return true;
        }

        public static IDbConnection CreatDbConnecttion()
        {
            return dbs.DbConnecttion;
        }

        public static int InsertCallBack<T>(T t) where T : class
        {
            var db = dbs.DbConnecttion;
            var sql = SqlQuery<T>.Builder(dbs);
            var a = db.Query<AutoNum>(sql.InsertSql+ "; Select  SCOPE_IDENTITY() as AutoId",t).FirstOrDefault();
            return a.AutoId ;
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool Insert<T>(T t, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var sql = SqlQuery<T>.Builder(dbs);
            var flag = db.Execute(sql.InsertSql, t, transaction, commandTimeout);
            return flag > 0;
        }

        /// <summary>
        ///  批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="lt"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool InsertBatch<T>(IList<T> lt, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var sql = SqlQuery<T>.Builder(dbs);
            var flag = db.Execute(sql.InsertSql, lt, transaction, commandTimeout);
            return flag > 0
                ;
        }

        /// <summary>
        /// 按条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool Delete<T>(SqlQuery sql = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            var f = db.Execute(sql.DeleteSql, sql.Param, transaction, commandTimeout);
            return f >= 0;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">按条件修改</param>
        /// <returns></returns>
        public static bool Update<T>(T t, SqlQuery sql = null) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            sql = sql.AppendParam<T>(t);
            var f = db.Execute(sql.UpdateSql, sql.Param);
            return f > 0;
        }
        /// <summary>
        /// 修改，可加事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>

        /// <returns></returns>
        public static bool Update<T>(T t, IDbTransaction transaction, int? commandTimeout = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var sql = SqlQuery<T>.Builder(dbs);
            sql.AppendParam<T>(t);
            var f = db.Execute(sql.UpdateSql, sql.Param, transaction, commandTimeout);
            return f > 0;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t">如果sql为null，则根据t的主键进行修改</param>
        /// <param name="ignoreProperties">要忽略修改的属性集合</param>
        /// <param name="sql">按条件修改</param>
        /// <returns></returns>
        public static bool Update<T>(T t, IList<string> ignoreProperties, SqlQuery sql = null) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            sql = sql.AppendParam<T>(t)
                .SetIgProperties<T>(ignoreProperties);
            var f = db.Execute(sql.UpdateSql, sql.Param);
            return f > 0;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t">如果sql为null，则根据t的主键进行修改</param>
        /// <param name="validProperties">要修改的属性集合</param>
        /// <param name="sql">按条件修改</param>
        /// <returns></returns>
        public static bool UpdateValid<T>(T t, IList<string> validProperties, SqlQuery sql = null) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            sql = sql.AppendParam<T>(t)
                .SetValidProperties<T>(validProperties);
            var f = db.Execute(sql.UpdateSql, sql.Param);
            return f > 0;
        }
        /// <summary>
        /// 获取默认一条数据，没有则为NULL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T SingleOrDefault<T>(SqlQuery sql) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            sql = sql.Top(1);
            var result = db.Query<T>(sql.QuerySql, sql.Param);
            return result.FirstOrDefault();
        }

        public static T CustomSingleOrDefault<T>(string customSql, SqlQuery sqlQuery = null, string customColumn = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var result = new List<T>();
            if (sqlQuery == null)
            {
                sqlQuery = SqlQuery<T>.Builder(dbs);
            }
            sqlQuery = sqlQuery.CustomColumn(customColumn);
            sqlQuery = sqlQuery.Custom(customSql);
            var para = sqlQuery.Param;
            result = db.Query<T>(sqlQuery.CustomQuerySql, para).ToList();
            return result.FirstOrDefault();
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static IList<T> QueryByPage<T>(int pageIndex, int pageSize, out int dataCount, SqlQuery sqlQuery = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var result = new List<T>();
            dataCount = 0;
            if (sqlQuery == null)
            {
                sqlQuery = SqlQuery<T>.Builder(dbs);
            }
            sqlQuery = sqlQuery.Page(pageIndex, pageSize);
            var para = sqlQuery.Param;
            var cr = db.Query(sqlQuery.CountSql, para).SingleOrDefault();
            dataCount = (int)cr.DataCount;
            result = db.Query<T>(sqlQuery.PageSql, para).ToList();

            return result;
        }
        /// <summary>
        /// 自定义sql查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="customSql"></param>
        /// <param name="sqlQuery"></param>
        /// <param name="customColumn"></param>
        /// <returns></returns>
        public static IList<T> CustomQuery<T>(string customSql, SqlQuery sqlQuery = null, string customColumn = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var result = new List<T>();
            if (sqlQuery == null)
            {
                sqlQuery = SqlQuery<T>.Builder(dbs);
            }
            sqlQuery = sqlQuery.CustomColumn(customColumn);
            sqlQuery = sqlQuery.Custom(customSql);
            var para = sqlQuery.Param;
            result = db.Query<T>(sqlQuery.CustomQuerySql, para).ToList();
            return result;
        }
        /// <summary>
        /// 自定义sql分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="customSql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static IList<T> CustomQueryByPage<T>(string customSql, int pageIndex, int pageSize, out int dataCount, SqlQuery sqlQuery = null, string customColumn = null) where T : class
        {
            var db = dbs.DbConnecttion;
            var result = new List<T>();
            dataCount = 0;
            if (sqlQuery == null)
            {
                sqlQuery = SqlQuery<T>.Builder(dbs);
            }
            sqlQuery = sqlQuery.CustomColumn(customColumn);
            sqlQuery = sqlQuery.Page(pageIndex, pageSize);
            sqlQuery = sqlQuery.Custom(customSql);
            var para = sqlQuery.Param;
            var cr = db.Query(sqlQuery.CustomCountSql, para).SingleOrDefault();
            dataCount = (int)cr.DataCount;
            result = db.Query<T>(sqlQuery.CustomPageSql, para).ToList();

            return result;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IList<T> Query<T>(SqlQuery sql = null) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            var result = db.Query<T>(sql.QuerySql, sql.Param);
            return result.ToList();
        }

        /// <summary>
        /// 数据数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static long Count<T>(SqlQuery sql = null) where T : class
        {
            var db = dbs.DbConnecttion;
            if (sql == null)
            {
                sql = SqlQuery<T>.Builder(dbs);
            }
            var cr = db.Query(sql.CountSql, sql.Param).SingleOrDefault();
            return (long)cr.DataCount;
        }

        public static bool Transaction(IList<SqlQuery> list, int? commandTimeout = null)
        {
            var result = false;
            var db = dbs.DbConnecttion;
            if (db.State == ConnectionState.Closed)
            {
                db.Open();
            }
            if (list.Count <= 0) return false;
            IDbTransaction transaction = db.BeginTransaction();
            try
            {
                foreach (var sql in list)
                {
                    if (db.Execute(sql.ToString(), sql.Param, transaction, commandTimeout) > 0)
                    {
                        result = true;
                    }
                    else
                    {
                        result = false;
                        break;
                    }
                    transaction.Commit();
                }
            }
            catch (Exception)
            {
                result = false;
                throw;
            }
            return result;
        }

        private static PropertyInfo GetAndRemove(string name, List<PropertyInfo> props)
        {

            foreach (var prop in props)
            {
                if (prop.Name.ToUpper() == name.ToUpper())
                {
                    props.Remove(prop);
                    return prop;

                }
            }
            return null;
        }
        public static IList<T2> CopyToModelList<T1, T2>(IList<T1> source) {
            if (source != null) { 
              T2 model = default(T2);

              PropertyInfo[] pi = typeof(T2).GetProperties();

              PropertyInfo[] pi1 = typeof(T1).GetProperties();

              var sourceList = new List<PropertyInfo>(pi1);
              List<T2> list = new List<T2>();
              
              foreach (T1 t1model in source)
              {
                  model = Activator.CreateInstance<T2>();
                  foreach (var prop in pi)
                  {
                      //目标实体对象属性名
                      var directName = prop.Name.ToUpper();
                      if (prop.CanWrite)
                      {
                          var sourceProp = GetAndRemove(directName, sourceList);
                          if (sourceProp != null)
                          {
                              prop.SetValue(model, sourceProp.GetValue(t1model, null), null);
                          }
                      }
                  }
                  if (model != null)
                      list.Add(model);
              }
              return list;
            }
            return null;
        }

        public static T2 CopyToModel<T1, T2>(T1 source)
        {
            T2 model = default(T2);

            PropertyInfo[] pi = typeof(T2).GetProperties();

            PropertyInfo[] pi1 = typeof(T1).GetProperties();

            var sourceList = new List<PropertyInfo>(pi1);

            model = Activator.CreateInstance<T2>();

            foreach (var prop in pi)
            {
                //目标实体对象属性名
                var directName = prop.Name.ToUpper();
                if (prop.CanWrite)
                {
                    var sourceProp = GetAndRemove(directName, sourceList);
                    if (sourceProp != null)
                    {
                        prop.SetValue(model,sourceProp.GetValue(source,null),null);
                    }
                }
            }

            return model;
        }
        public static IList<T> Query<T>(string sql) where T : class
        {
            var db = dbs.DbConnecttion;
            var result = db.Query<T>(sql).ToList();
            return result;
        }
    }
}